/* WifiDB.m - The Wifi AP measurement log database.
 *
 * Copyright (C) 2008 Yuuichi Teranishi
 *
 * This file is derived from Stumbler, a program developed by iphone-wireless
 * project.
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License version 2
 * published by the Free Software Foundation.

 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.

 * You should have received a copy of the GNU General Public License
 * along with this program; If not, write to the Free Software Foundation,
 * Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
 */
#import "WifiDB.h"

static NSObject *str2date(char *str) {
  int ret;
  sscanf(str, "%d", &ret);
  return [NSCalendarDate dateWithTimeIntervalSince1970: ret];
}

static NSObject *str2int (char *str) {
  int ret;
  sscanf(str, "%d", &ret);
  return [NSNumber numberWithInt: ret];
}

static NSObject *str2double(char *str) {
  float ret;
  sscanf(str, "%f", &ret);
  return [NSNumber numberWithFloat: ret];
}

static NSString *str2string(char *str) {
  return [NSString stringWithCString: str encoding: NSUTF8StringEncoding];
}

static int query_callback(void *appData,
			  int argc,
			  char **argv,
			  char **azColName){
  int i;
  NSMutableArray *result = (NSMutableArray *)appData;
  NSMutableDictionary *entry = [[NSMutableDictionary alloc] init];

  for(i = 0; i < argc; i++){
    //    printf("%s = %s\n", azColName[i], argv[i] ? argv[i]: "NULL");
    NSObject *val;
    if (strcmp(azColName[i], "mac") == 0) {
      val = argv[i]? str2string(argv[i]) : nil; 
    }
    else if (strcmp(azColName[i], "id") == 0) {
      val = argv[i]? str2int(argv[i]) : nil; 
    }
    else if (strcmp(azColName[i], "wep") == 0) {
      val = argv[i]? str2string(argv[i]) : nil; 
    }
    else if (strcmp(azColName[i], "lng") == 0) {
      val = argv[i]? str2double(argv[i]) : nil; 
    }
    else if (strcmp(azColName[i], "lat") == 0) {
      val = argv[i]? str2double(argv[i]) : nil;
    }
    else if (strcmp(azColName[i], "date") == 0) {
      val = argv[i]? str2date(argv[i]) : nil;
    }
    else if (strcmp(azColName[i], "floor") == 0) {
      val = argv[i]? str2int(argv[i]) : nil;
    }
    else if (strcmp(azColName[i], "precision") == 0) {
      val = argv[i]? str2int(argv[i]) : nil;
    }
    else if (strcmp(azColName[i], "stat") == 0) {
      val = argv[i]? str2int(argv[i]) : nil;
    }
    else if (strcmp(azColName[i], "signal") == 0) {
      val = argv[i]? str2int(argv[i]) : nil;
    }
    
    [entry setObject: val
	   forKey:[NSString stringWithCString: azColName[i]
			    encoding: NSUTF8StringEncoding]];
  }
  [result addObject: entry];
  //  printf("\n");
  return 0;
}

// A = 100, B = 40
static double dist (double sig) {
  return pow (10, (100 - sig) / 40);
}

@implementation WifiDB

- (NSMutableArray *)execQuerySQL:(NSString *)query {
  sqlite3 *db;
  char *zErrMsg = 0;
  int rc;

  NSMutableArray *result = [[NSMutableArray alloc] init];
    
  rc = sqlite3_open([_file cStringUsingEncoding: NSUTF8StringEncoding], &db);
  if( rc ){
    [_delegate showAlert: [NSString stringWithFormat:
				      @"Can't open database: %d, %s\n",
				    rc, sqlite3_errmsg(db)]];
    sqlite3_close(db);
    return;
  }
  rc = sqlite3_exec(db, [query cStringUsingEncoding: NSUTF8StringEncoding],
		    query_callback, result, &zErrMsg);
  if( rc!=SQLITE_OK ){
    NSLog([NSString stringWithFormat:
		      @"SQL error: %d, %s\n",
		    rc, zErrMsg]);
  }
  sqlite3_close(db);
  return result;
}

- (int)execSQL:(NSString *)query {
  sqlite3 *db;
  char *zErrMsg = 0;
  int rc;

  rc = sqlite3_open([_file cStringUsingEncoding: NSUTF8StringEncoding], &db);
  if( rc ){
    [_delegate showAlert: [NSString stringWithFormat:
				    @"Can't open database: %d, %s\n",
				    rc, sqlite3_errmsg(db)]];
    sqlite3_close(db);
    return;
  }
  rc = sqlite3_exec(db, [query cStringUsingEncoding: NSUTF8StringEncoding],
		    NULL, 0, &zErrMsg);
  sqlite3_close(db);
  return rc;
}

- (int)foundAPWithBSSID:(NSString *) bssid ssid: (NSString *)ssid lng:(double) lng lat:(double) lat floor: floor signal:(int) signal {
  int ret;
  [self execSQL:
	  // XXX must treat sql injection...
	  [@"insert into WifiLog (mac, wep, lng, lat, floor, signal, date, precision, stat) VALUES"
	    stringByAppendingFormat:
	      @"(\"%@\", \"%@\", %f, %f, %d, %d, %d, %d, %d)",
	    bssid,
	    ssid,
	    lng,
	    lat,
	    floor,
	    signal,
	    (int)[[[NSDate alloc] init] timeIntervalSince1970],
	    0, 0 // precision and stat are not used yet
	   ]]; 
  NSMutableArray *arr = [self searchLogByBSSID: bssid ssid: ssid];
  int i;
  double x = 0;
  double y = 0;
  int f = 0;
  double w = 0;
  double xp = 0;
  double yp = 0;
  double fp = 0;
  double d = 0;
  double ds = 0;
  for (i = 0; i < [arr count]; i++) {
    NSDictionary *ap = [arr objectAtIndex: i];
    d = (double) dist([[ap objectForKey:@"signal"] doubleValue]);
    ds += d;
    w += 1 / d;
    xp += (1 / d) * [[ap objectForKey:@"lng"] doubleValue];
    yp += (1 / d) * [[ap objectForKey:@"lat"] doubleValue];
    fp += (1 / d) * [[ap objectForKey:@"floor"] intValue];
  }
  x = xp / w;
  y = yp / w;
  f = round(fp / w);
  [arr release];
  // update APLocation..
  arr = [self searchAPByBSSID: bssid ssid: ssid];
  if ([arr count] == 0) {
    [self execSQL:
	    // XXX must treat sql injection...
	    [@"insert into APLocation (mac, wep, lng, lat, floor) VALUES"
	      stringByAppendingFormat:
		@"(\"%@\", \"%@\", %f, %f, %d)",
	      bssid,
	      ssid,
	      x,
	      y,
	      f]];
  }
  else if ([arr count] == 1) {
    [self execSQL:
	    // XXX must treat sql injection...
	    [@"update APLocation set "
	      stringByAppendingFormat:
		@"lng = %f, lat = %f, floor = %d where id=%d",
	      x,
	      y,
	      f,
	      [[[arr objectAtIndex: 0] objectForKey:@"id"] intValue]]];
  }
  else {
    NSLog(@"unexpected situation.");
  }
  ret = [arr count];
  [arr release];
  return ret;
}

- (NSMutableArray *)searchLogByBSSID:(NSString *) bssid ssid: (NSString *)ssid {
  //  NSLog([@"select * from WifiLog where "
  //	  stringByAppendingFormat:
  //	    @"mac = \"%@\" and wep = \"%@\"",
  //	  bssid, ssid]);
  return
    [self 
      execQuerySQL:
	[@"select * from WifiLog where "
	  stringByAppendingFormat:
	    @"mac = \"%@\" and wep = \"%@\"",
	  bssid, ssid]];
}

- (NSMutableArray *)searchAPByBSSID:(NSString *) bssid ssid: (NSString *)ssid {
  //  NSLog([@"select * from APLocation where "
  //	  stringByAppendingFormat:
  //	    @"mac = \"%@\" and wep = \"%@\"",
  //	  bssid, ssid]);
  return
    [self 
      execQuerySQL:
	[@"select * from APLocation where "
	  stringByAppendingFormat:
	    @"mac = \"%@\" and wep = \"%@\"",
	  bssid, ssid]];
}

- (APLocation *)getAPLocation:(NSString *) bssid ssid:(NSString *) ssid signal:(int) signal {
  NSMutableArray *arr = [self searchAPByBSSID: bssid ssid: ssid];
  if ([arr count] == 1) {
    return [[APLocation alloc] initWithLat:
				 [[[arr objectAtIndex: 0] objectForKey:@"lat"] doubleValue]
			       lng:
				 [[[arr objectAtIndex: 0] objectForKey:@"lng"] doubleValue]
			       floor: 
				 [[[arr objectAtIndex: 0] objectForKey:@"floor"] intValue]
			       signal: signal];
  }
  return nil;
}

- (WifiDB *)initWithFile:(NSString *) file {
  _file = file;
  [self execSQL:@"create table WifiLog( \
id INTEGER PRIMARY KEY, \
mac TEXT, \
wep TEXT, \
lng DOUBLE PRECISION, \
lat DOUBLE PRECISION, \
floor INTEGER, \
signal INTEGER, \
date DOUBLE PRECISION, \
precision INTEGER, \
stat INTEGER)"];
  [self execSQL:@"create table APLocation( \
id INTEGER PRIMARY KEY,\
mac TEXT, \
wep TEXT, \
lng DOUBLE PRECISION, \
lat DOUBLE PRECISION, \
floor INTEGER)"];
  [self execSQL:@"create index log_id_idx on WifiLog(mac text, wep text)"];
  [self execSQL:@"create index loc_id_idx on APLocation(mac text, wep text)"];
  return self;
}

- (void)setAlertDelegate:(id)delegate {
}
@end
